using System;
using System.Data;
using System.Collections;
using BEGDC.CC.Entity;
using BEGDC.CC.BL.Common;
using BEGDC.CC.BL.Common.Query;
using NHibernate;
using BEGDC.Utility.Castle;
using BEGDC.CC.BL.CCFlow.CR;

namespace BEGDC.CC.ExcelTool
{
	/// <summary>
	/// Summary description for ExportECSCVForm.
	/// </summary>
	public class ExportECSCVForm:ICCExcelExport
	{
		private string templateName = System.Configuration.ConfigurationSettings.AppSettings["ECSCVFormTemplate"];
		private string targetFileName = System.Configuration.ConfigurationSettings.AppSettings["ExportPath"];
		private string fileName;

		private IExcelExport excelExport;

		public ExportECSCVForm()
		{
			this.excelExport = new ExcelExportCommom();
		}
		public ExportECSCVForm(IExcelExport excelExport)
		{
			this.excelExport = excelExport;
		}

		private void GenerateFileName()
		{
			DateTime tmpTime=DateTime.Now;
			this.fileName+=tmpTime.Millisecond.ToString();
			this.fileName+=tmpTime.Second.ToString();
			this.fileName+=tmpTime.Minute.ToString();
			this.fileName+="_ECSCVForm.xls";
		}

		public void GenerateECSCVForm(int appId )
		{
			try
			{
				this.excelExport.Open( templateName );

				BEGDC.CC.BL.Common.Query.IQuery queryService =  AppContainer.Instance[ServiceConst.QueryService] as BEGDC.CC.BL.Common.Query.IQuery;
								
				//Fill in application information
				//including AppNo
				//RequsterInfo--PART1,  Someelse PART2, RequestedDate info - PART7 Visit Site - PART4
				GenerateAppInfo(queryService, appId );
			
				//Fill in customer information PART3, PART5
				GenerateCustomerInfo (queryService, appId );

				//Fill Business purpose info PART6
				GenerateBusinessPurpose( queryService, appId );
			
				//Fill Topic info PART8
				GenerateTopic( queryService, appId );

				//Fill CustomerAttendee PART9
				GenerateCustomerAttendee( queryService, appId );
			
				//Fill ASBAttendee PART10
				GenerateASBAttendee( queryService, appId );
				//Fill Sales Team Info PART11
				GenerateSalesTeamInfo( queryService, appId );

				//Fill Logistical Info PART12, PART14
				GenerateLogisticalInfo( queryService, appId );

				//Fill Payment Info PART13
				GeneratePaymentInfo( queryService, appId );

				this.fileName=this.fileName.Replace("/","").Replace("\\","").Replace("'","").Replace("\"","");
				this.fileName=this.targetFileName + this.fileName;

				this.excelExport.ExportAs(this.fileName);
			}
			catch(Exception ex)
			{
				BEGDC.Utility.ExceptionManagement.GDCException exp=new BEGDC.Utility.ExceptionManagement.GDCException("export_0001",ex.Message,ex.InnerException);
				throw exp;
			}
			finally
			{
				this.excelExport.Close();
			}
		}

		
		private void GenerateAppInfo(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId )
		{
			Application app = queryService.GetAppById( appId );
		
			if( app == null )
				return;
			
			BEGDC.CC.BL.Common.IUtility utility = AppContainer.Instance[ServiceConst.UtilityService] as BEGDC.CC.BL.Common.IUtility;
			IList list;
			//set EZvisitID
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("EZVisitID", Type.Missing );
			this.excelExport.MyApp.get_Range( rngTemp.Cells[1,1], rngTemp.Cells[1,1]).Value2 = app.EZVisitReqNo;
			//set AppNo
			string[] strList;
			rngTemp = this.excelExport.MyApp.get_Range("AppNo", Type.Missing );
			this.excelExport.MyApp.get_Range( rngTemp.Cells[1,1], rngTemp.Cells[1,1]).Value2 = app.AppNo;
			this.fileName =app.AppNo + "_" + this.fileName;

			#region set Requestor information

			rngTemp = this.excelExport.MyApp.get_Range("RequestorInfo", Type.Missing );

			if (app.RequesterNameEN==string.Empty || app.RequesterNameEN=="" || app.RequesterNameEN==null)
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = app.RequesterNameCN;
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = app.RequesterNameEN;

			this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = app.RequesterOfficeTel;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 = app.RequesterMobile;
			//Add by belinda 20070806.
			if(app.IsSSME)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = "SSME";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[5,3], rngTemp.Cells[5,3]).Value2 = app.CostCenterCode.DicItemKey;
			}
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = "Not SSME";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[6,3], rngTemp.Cells[6,3]).Value2 = app.DepartmentType.DicItemKey;
			}
			//end.
			if (app.LegalEntity != null)
				this.excelExport.MyApp.get_Range( rngTemp.Cells[7,3], rngTemp.Cells[7,3]).Value2 = app.LegalEntity.LegalEntityName;

			if (app.ApplyForSomeoneElse)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[8,3], rngTemp.Cells[8,3]).Value2 = "Yes";
				//set Someone else info
				rngTemp = this.excelExport.MyApp.get_Range("SomeoneElse", Type.Missing );
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = app.SomeoneElseHRID;
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = app.SomeoneElseNameEN;
				this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 = app.SomeoneElseTel;
				this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = app.SomeoneElseMobil;
				this.excelExport.MyApp.get_Range( rngTemp.Cells[5,3], rngTemp.Cells[5,3]).Value2 = app.SomeoneElseEmail;
			}
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[8,3], rngTemp.Cells[8,3]).Value2 = "No";
			}
			#endregion
			
			#region set Request Date -- Part 8
			//PART8 Request Date
			rngTemp = this.excelExport.MyApp.get_Range("DateInfo", Type.Missing );
			if (app.RequestedDateFrom != Nullables.NullableDateTime.Default)
			{
				if (app.RequestedDateTo != Nullables.NullableDateTime.Default)
					this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = ((System.DateTime)app.RequestedDateFrom.Value).ToString("MM/dd/yyyy") + " - " + ((System.DateTime)app.RequestedDateTo.Value).ToString("MM/dd/yyyy");
				else
					this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = ((System.DateTime)app.RequestedDateFrom.Value).ToString("MM/dd/yyyy") ;
			}
			else
			{
				if (app.RequestedDateTo != Nullables.NullableDateTime.Default)
					this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = " - " + ((System.DateTime)app.RequestedDateTo.Value).ToString("MM/dd/yyyy");
			}
//			//PART8 Alternate Date  ---Add by belinda 20070807
//			if (app.AlternateDateFrom != Nullables.NullableDateTime.Default)
//			{
//				if (app.AlternateDateTo != Nullables.NullableDateTime.Default)
//					this.excelExport.MyApp.get_Range(rngTemp.Cells[2, 3], rngTemp.Cells[2, 3]).Value2 = ((System.DateTime)app.AlternateDateFrom.Value).ToString("MM/dd/yyyy") + " - " + ((System.DateTime)app.AlternateDateTo.Value).ToString("MM/dd/yyyy");
//				else
//					this.excelExport.MyApp.get_Range(rngTemp.Cells[2, 3], rngTemp.Cells[2, 3]).Value2 = ((System.DateTime)app.AlternateDateFrom.Value).ToString("MM/dd/yyyy");
//			}
//			else
//			{
//				if (app.AlternateDateTo != Nullables.NullableDateTime.Default)
//					this.excelExport.MyApp.get_Range(rngTemp.Cells[2, 3], rngTemp.Cells[2, 3]).Value2 = " - " + ((System.DateTime)app.AlternateDateTo.Value).ToString("MM/dd/yyyy");
//			}
			#endregion

			#region preRequest review guide, other info in customerinfo
			rngTemp = this.excelExport.MyApp.get_Range("PreRequestInfo", Type.Missing );			
			if (app.IsReviewedGuideline)
				this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = "Yes";
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = "No";
			#endregion

			#region BUHeadApprove Info -- Part 5
			rngTemp = this.excelExport.MyApp.get_Range("BUHeadInfo", Type.Missing );			
			if (app.BUHeadApproved)
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = "Yes";
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = "No";
			#endregion

			#region set visit site info
			rngTemp = this.excelExport.MyApp.get_Range("VisitSiteInfo", Type.Missing );
			
			DataTable dtSite = queryService.GetVisitingSite(appId);
			if (dtSite.Rows.Count >0)
			{
				list = utility.BuildDicList( BEGDC.CC.BL.Common.DicType.OCOOversea);
				for (int i=0;i<dtSite.Rows.Count;i++)
				{
					for (int j=0;j<list.Count;j++)
					{
						if (dtSite.Rows[i]["DicItemID"].ToString()==((DicItem)list[j]).Id.ToString())
						{
							this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 += (i+1).ToString() + ") " + ((DicItem)list[j]).DicItemKey + "\n";				
							break;
						}
					}
				}

			}
			this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 += app.OtherCountry;				

			#endregion
		}
		
		private string[] SplitList(string strToSplit)
		{
			string[] temp;
			temp=strToSplit.Split(',');
			return temp;
		}

		private void GenerateCustomerAttendee(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId )
		{
			DataTable custAttendee = queryService.QueryCustomerAttendeeByAppId( appId );
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("CustomerAttendee", Type.Missing);
			int rowCount=2;
			while (custAttendee.Rows.Count> rowCount)
			{
				this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Copy(Type.Missing);
				this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
				rowCount++;
			}

			rngTemp = this.excelExport.MyApp.get_Range("CustomerAttendee", Type.Missing);

			if( custAttendee != null && custAttendee.Rows.Count > 0 )
			{
				for( int i=0; i<custAttendee.Rows.Count; i++)
				{
					//prefer NameEN
					if( custAttendee.Rows[i]["NameEN"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,1], rngTemp.Cells[i+1,1]).Value2 = custAttendee.Rows[i]["NameEN"].ToString();
					
					if( custAttendee.Rows[i]["CompanyEN"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,3], rngTemp.Cells[i+1,3]).Value2 = custAttendee.Rows[i]["CompanyEN"].ToString();
				
					if( custAttendee.Rows[i]["TitleEN"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,4], rngTemp.Cells[i+1,4]).Value2 = custAttendee.Rows[i]["TitleEN"].ToString();

					if( custAttendee.Rows[i]["Responsibility"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,5], rngTemp.Cells[i+1,5]).Value2 = custAttendee.Rows[i]["Responsibility"].ToString();
				
					if( custAttendee.Rows[i]["Affects"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,6], rngTemp.Cells[i+1,6]).Value2 = custAttendee.Rows[i]["Affects"].ToString();

					if( custAttendee.Rows[i]["Remark"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,7], rngTemp.Cells[i+1,7]).Value2 = custAttendee.Rows[i]["Remark"].ToString();				
				}
			}

		}

		private void GenerateASBAttendee( BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			DataTable alAttendee = queryService.QueryALAttendeeByAppId( appId );
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("ALAttendee", Type.Missing);
			int rowCount=2;
			while (alAttendee.Rows.Count> rowCount)
			{
				this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Copy(Type.Missing);
				this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
				rowCount++;
			}

			rngTemp = this.excelExport.MyApp.get_Range("ALAttendee", Type.Missing);

			if( alAttendee != null && alAttendee.Rows.Count > 0 )
			{
				for( int i=0; i<alAttendee.Rows.Count; i++)
				{
					if( alAttendee.Rows[i]["NameEN"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,1], rngTemp.Cells[i+1,1]).Value2 = alAttendee.Rows[i]["NameEN"].ToString();
					if( alAttendee.Rows[i]["Title"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,3], rngTemp.Cells[i+1,3]).Value2 = alAttendee.Rows[i]["Title"].ToString();					
				}
			}
		}

		private void GenerateCustomerInfo(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			BEGDC.CC.Entity.CustomerInfo custInfo=queryService.GetCustomerInfo(appId);
			if (custInfo==null)
				return;
		
			BEGDC.CC.BL.Common.IUtility utility = AppContainer.Instance[ServiceConst.UtilityService] as BEGDC.CC.BL.Common.IUtility;
			IList list;

		
			//set customer info
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("CustomerInfo", Type.Missing );
			this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = custInfo.NumberOfPerson;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = custInfo.VisitObjective;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 = custInfo.DecryptedRevenuePotential;
			//look up
			list = utility.BuildDicList( BEGDC.CC.BL.Common.DicType.POTENTIAL_REVENUE_TIMEFRAME);
			for (int i=0;i<list.Count;i++)
			{
				if (((DicItem)list[i]).Id == custInfo.PotentialRevenueTimeframe)
				{
					this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2=((DicItem)list[i]).DicItemKey;
					break;					
				}
			}

			list = utility.BuildDicList( BEGDC.CC.BL.Common.DicType.SALES_STAGE);
			for (int i=0;i<list.Count;i++)
			{
				if (((DicItem)list[i]).Id == custInfo.SalesStage)
				{
					this.excelExport.MyApp.get_Range( rngTemp.Cells[5,3], rngTemp.Cells[5,3]).Value2=((DicItem)list[i]).DicItemKey;
					break;					
				}
			}
			//
			if (custInfo.IsInRFP)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[6,3], rngTemp.Cells[6,3]).Value2 = "Yes";
//				this.excelExport.MyApp.get_Range( rngTemp.Cells[7,3], rngTemp.Cells[7,3]).Value2 =custInfo.RFPDetail;
			}
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[6,3], rngTemp.Cells[6,3]).Value2 = "No";
			}
			if (custInfo.IsSensitiveIssue)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[7,3], rngTemp.Cells[7,3]).Value2="Yes";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[8,3], rngTemp.Cells[8,3]).Value2=custInfo.IsThereSensitiveIssue;
			}
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[7,3], rngTemp.Cells[7,3]).Value2="No";
			}

			if (custInfo.IsKeyMessage)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[9,3], rngTemp.Cells[9,3]).Value2="Yes";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[10,3], rngTemp.Cells[10,3]).Value2=custInfo.IsThereKeyMessage;
			}
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[9,3], rngTemp.Cells[9,3]).Value2="No";
			}

			this.excelExport.MyApp.get_Range( rngTemp.Cells[11,3], rngTemp.Cells[11,3]).Value2 = custInfo.CompanyProfile;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[12,3], rngTemp.Cells[12,3]).Value2 = custInfo.AccountType.Description;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[13,3], rngTemp.Cells[13,3]).Value2 = custInfo.ProposalNo;

			rngTemp = this.excelExport.MyApp.get_Range("PreRequestInfo", Type.Missing );
			this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = custInfo.Company;
			if (custInfo.IsFCPA)
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = "Yes";
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = "No";
			this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 = custInfo.Country;

			//is review guide line is in app table
		}

		private void GenerateBusinessPurpose(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			BEGDC.CC.Entity.BusinessPurpose busPurpose=queryService.GetBusinessPurposeInfo(appId);
			if (busPurpose==null)
				return;
			
			//set Topic info
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("BusinessPurpose", Type.Missing );
			if (busPurpose.IsInSalesContract)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 ="Yes";
				if (busPurpose.IsContractSigned)
				{
					this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 ="Yes";
				}
				else
				{
					this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 ="No";
				}
			}
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 ="No";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 =busPurpose.Opportunities;

			}

			#region Contract info
			int rowCount=2;
			double totalPrice=0;
			rngTemp = this.excelExport.MyApp.get_Range("ContractInfo", Type.Missing );
			DataTable dtContract=queryService.QueryContractInfoByAppId(appId);
			if (dtContract.Rows.Count>0)
			{
				
				while (dtContract.Rows.Count > rowCount)
				{
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Copy(Type.Missing);
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
					rowCount++;
				}

				rngTemp = this.excelExport.MyApp.get_Range("ContractInfo", Type.Missing );		
				for( int i=0; i < dtContract.Rows.Count; i++)
				{
					if( dtContract.Rows[i]["DateOfContract"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,1], rngTemp.Cells[i+1,1]).Value2=((System.DateTime)dtContract.Rows[i]["DateOfContract"]).ToString("yyyy-MM-dd");
					if( dtContract.Rows[i]["ContractNo"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,2], rngTemp.Cells[i+1,2]).Value2=dtContract.Rows[i]["ContractNo"].ToString();

					if( dtContract.Rows[i]["ProducstSold"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,3], rngTemp.Cells[i+1,3]).Value2=dtContract.Rows[i]["ProducstSold"].ToString();
					if( dtContract.Rows[i]["VisitPurposeInContract"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,4], rngTemp.Cells[i+1,4]).Value2=dtContract.Rows[i]["VisitPurposeInContract"].ToString();
					if( dtContract.Rows[i]["Quotas"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,5], rngTemp.Cells[i+1,5]).Value2=dtContract.Rows[i]["Quotas"].ToString();
					
					if( dtContract.Rows[i]["UnitPrice"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,6], rngTemp.Cells[i+1,6]).Value2=dtContract.Rows[i]["UnitPrice"].ToString();
					
					if( dtContract.Rows[i]["Budget"] != System.DBNull.Value )
					{
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,7], rngTemp.Cells[i+1,7]).Value2=dtContract.Rows[i]["Budget"].ToString();
						totalPrice+=Convert.ToDouble(dtContract.Rows[i]["Budget"].ToString());
					}
				}
			}
			#endregion
			
			rowCount++;
			this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount,7], rngTemp.Cells[rowCount,7]).Value2 = totalPrice;

			
			if (busPurpose.RecreationalActivity)
			{
				this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+1,3], rngTemp.Cells[rowCount+1,3]).Value2 = "Yes";
			}
			else
			{
				this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+1,3], rngTemp.Cells[rowCount+1,3]).Value2 = "No";
			}
			
			if (busPurpose.IsNeedInvitationLetter)
			{
				this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+2,3], rngTemp.Cells[rowCount+2,3]).Value2 = "Yes";
			}
			else
			{
				this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+2,3], rngTemp.Cells[rowCount+2,3]).Value2 = "No";
			}	
		}

		private void GenerateTopic(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			BEGDC.CC.Entity.Topic topic=queryService.GetTopicInfo(appId);
			if (topic==null)
				return;
			
			//set Topic info
			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("Topic", Type.Missing );
			this.excelExport.MyApp.get_Range( rngTemp.Cells[1,1], rngTemp.Cells[1,1]).Value2 = topic.TopicName;
		}

		private void GenerateSalesTeamInfo(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			BEGDC.CC.Entity.SalesTeamInfo salesTeam=queryService.GetSalesTeamInfo(appId);
			if (salesTeam==null)
				return;
			BEGDC.CC.BL.Common.IUtility utility = AppContainer.Instance[ServiceConst.UtilityService] as BEGDC.CC.BL.Common.IUtility;
			IList list;

			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("SalesTeam", Type.Missing );
			this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = salesTeam.HostNameEN;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = salesTeam.OfficeTel;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 = salesTeam.OfficeFax;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = salesTeam.Email;

			list = utility.BuildDicList( BEGDC.CC.BL.Common.DicType.SALES_BUSINESS_UNIT_OCO);
			for (int i=0;i<list.Count;i++)
			{
				if (((DicItem)list[i]).Id.ToString() == salesTeam.SalesBusinessUnit)
				{
					this.excelExport.MyApp.get_Range( rngTemp.Cells[5,3], rngTemp.Cells[5,3]).Value2=((DicItem)list[i]).DicItemKey;
					break;					
				}
			}
			//this.excelExport.MyApp.get_Range( rngTemp.Cells[5,3], rngTemp.Cells[5,3]).Value2 = salesTeam.SalesBusinessUnit;

			list = utility.BuildDicList( BEGDC.CC.BL.Common.DicType.REGION_OCO);
			for (int i=0;i<list.Count;i++)
			{
				if (((DicItem)list[i]).Id.ToString() == salesTeam.Region)
				{
					this.excelExport.MyApp.get_Range( rngTemp.Cells[6,3], rngTemp.Cells[6,3]).Value2=((DicItem)list[i]).DicItemKey;
					break;					
				}
			}

			//this.excelExport.MyApp.get_Range( rngTemp.Cells[6,3], rngTemp.Cells[6,3]).Value2 = salesTeam.Region;
		
		}

		private void GenerateLogisticalInfo(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			BEGDC.CC.Entity.LogisticalInfo lgsInfo=queryService.GetLogisticalInfo(appId);
			if (lgsInfo==null)
				return;

			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("LogisticalInfo", Type.Missing );
			
			//Entertainment
			if (lgsInfo.IsNeedEntertainment)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 ="Yes";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 =lgsInfo.EntertainmentPreference;		
				this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 = lgsInfo.DecryptedEntertainmentPriceFrom.ToString() + " - " + lgsInfo.DecryptedEntertainmentPriceTo.ToString();
			}
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 ="No";
			
			//gift
			if (lgsInfo.IsNeedGift)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 ="Yes";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[5,3], rngTemp.Cells[5,3]).Value2 =lgsInfo.GiftPreference;
			}	
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 ="No";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[5,3], rngTemp.Cells[5,3]).Value2 ="None";
			}

			this.excelExport.MyApp.get_Range( rngTemp.Cells[6,3], rngTemp.Cells[6,3]).Value2 = lgsInfo.DecryptedGiftPriceFrom.ToString() + " - " + lgsInfo.DecryptedGiftPriceTo.ToString();

			if (lgsInfo.IsReceivedALGiftBefore)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[7,3], rngTemp.Cells[7,3]).Value2 ="Yes";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[8,3], rngTemp.Cells[8,3]).Value2 =lgsInfo.ReceivedGiftDetails;
			}
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[7,3], rngTemp.Cells[7,3]).Value2 ="No";

			if (lgsInfo.IsNeedInterpreter)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[9,3], rngTemp.Cells[9,3]).Value2 ="Yes";

				//look up
				if (lgsInfo.InterpretionType==1)
					this.excelExport.MyApp.get_Range( rngTemp.Cells[10,3], rngTemp.Cells[10,3]).Value2 ="Simultaneous";
				else
					this.excelExport.MyApp.get_Range( rngTemp.Cells[10,3], rngTemp.Cells[10,3]).Value2 ="Consecutive";
			}
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[9,3], rngTemp.Cells[9,3]).Value2 ="No";

			if (lgsInfo.SpecialDialect !="" && lgsInfo.SpecialDialect !="0")
				this.excelExport.MyApp.get_Range( rngTemp.Cells[11,3], rngTemp.Cells[11,3]).Value2 ="Yes";
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[11,3], rngTemp.Cells[11,3]).Value2 = "No";

			//this.excelExport.MyApp.get_Range( rngTemp.Cells[17,3], rngTemp.Cells[17,3]).Value2 =lgsInfo.TravelAgency;
			
			//CateringRequest Info
			rngTemp = this.excelExport.MyApp.get_Range("CateringRequest", Type.Missing );
			if (lgsInfo.IsNeedCatering)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 ="Yes";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 =lgsInfo.CateringDescription;
			}
			else
				this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 ="No";			
		}

		private void GeneratePaymentInfo(BEGDC.CC.BL.Common.Query.IQuery queryService, int appId)
		{
			BEGDC.CC.Entity.PaymentInfo paymentInfo=queryService.GetPaymentInfo(appId);
			if (paymentInfo==null)
				return;
			BEGDC.CC.BL.Common.IUtility utility = AppContainer.Instance[ServiceConst.UtilityService] as BEGDC.CC.BL.Common.IUtility;
			IList list;

			Excel.Range rngTemp = this.excelExport.MyApp.get_Range("Payment", Type.Missing );

			list = utility.BuildDicList( BEGDC.CC.BL.Common.DicType.PAYER);
			for (int i=0;i<list.Count;i++)
			{
				if (((DicItem)list[i]).Id == paymentInfo.Payer)
				{
					this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2=((DicItem)list[i]).DicItemKey;
					break;					
				}
			}
			//this.excelExport.MyApp.get_Range( rngTemp.Cells[1,3], rngTemp.Cells[1,3]).Value2 = paymentInfo.Payer;
			if (paymentInfo.IsPayServiceProvider)
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = "Yes";
			}
			else
			{
				this.excelExport.MyApp.get_Range( rngTemp.Cells[2,3], rngTemp.Cells[2,3]).Value2 = "No";
				this.excelExport.MyApp.get_Range( rngTemp.Cells[3,3], rngTemp.Cells[3,3]).Value2 = paymentInfo.NotDirectPayReasons;
			}
			this.excelExport.MyApp.get_Range( rngTemp.Cells[4,3], rngTemp.Cells[4,3]).Value2 = paymentInfo.DecryptedEstimatedCustomerTotalCost;
			this.excelExport.MyApp.get_Range( rngTemp.Cells[5,3], rngTemp.Cells[5,3]).Value2 = paymentInfo.DecryptedEstimateCustomerdIndividualCost;

			//Cost center info
			int rowCount=2;
			double totalCost = 0;
			DataTable dtCost=queryService.GetCostCenter(paymentInfo.Id);
			rngTemp = this.excelExport.MyApp.get_Range("CostCenter", Type.Missing );

			if (dtCost.Rows.Count>0)
			{
				
				while (dtCost.Rows.Count > rowCount)
				{
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Copy(Type.Missing);
					this.excelExport.MyApp.get_Range(rngTemp.Cells[2,1],rngTemp.Cells[2,7]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
					rowCount++;
				}

				rngTemp = this.excelExport.MyApp.get_Range("CostCenter", Type.Missing);

				for( int i=0; i < dtCost.Rows.Count; i++)
				{
					if( dtCost.Rows[i]["CostCenterCode"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,1], rngTemp.Cells[i+1,1]).Value2=dtCost.Rows[i]["CostCenterCode"];
					if( dtCost.Rows[i]["Cost"] != System.DBNull.Value )
					{
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,3], rngTemp.Cells[i+1,3]).Value2=dtCost.Rows[i]["Cost"].ToString();
						totalCost += Convert.ToDouble(dtCost.Rows[i]["Cost"].ToString());
					}
					if( dtCost.Rows[i]["ActualCost"] != System.DBNull.Value )
						this.excelExport.MyApp.get_Range( rngTemp.Cells[i+1,6], rngTemp.Cells[i+1,6]).Value2=dtCost.Rows[i]["ActualCost"].ToString();
				}				
			}
			
			this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+1,7], rngTemp.Cells[rowCount+1,7]).Value2 = totalCost;
			this.excelExport.MyApp.get_Range(rngTemp.Cells[rowCount+2,3], rngTemp.Cells[rowCount+2,3]).Value2 = paymentInfo.CustomerPerdiem;		
		}

		#region ICCExcelExport Members

		public void Export(System.Web.UI.Page page, int id)
		{			
			//generate filename randomly
			GenerateFileName();

			this.GenerateECSCVForm( id );

			this.excelExport.Export2Client(page, this.fileName, "ECSCVForm.xls");
		}

		#endregion
	}
}
